<?php

class mmerchandiser extends CI_Model {

	
	function __construct()
	{
        // Call the Model constructor
		parent::__construct();
	}
	
	public function getOutlet( $date=null, $merchan_id =null, $clusterId, $row){

		$sql = "SELECT
		merchandiser_id 
		,mm.partner_id
		,oo.outlet_name
		,concat_ws (', ', oo.address, oo.city) as address
		,mm.keyword as key
		,img_outlet_with_keyword as img
				,(select to_char(tj.check_in_date,'YYYY-mm-dd HH24:II:SS')  from t_trx_journey tj where tj.user_id=mm.user_id and tj.channel_id=mm.partner_id and date(tj.check_in_date)=mm.merchandiser_date and type='checkin' ORDER BY tj.check_in_date desc limit 1) as checkin
,
COALESCE((select to_char(tj1.check_in_date,'YYYY-mm-dd HH24:II:SS') from t_trx_journey tj1 where tj1.user_id=mm.user_id and tj1.channel_id=mm.partner_id and date(tj1.check_in_date)=mm.merchandiser_date and type='checkout' ORDER BY tj1.check_in_date desc limit 1),'') as checkout

		FROM t_trx_merchandiser mm
		JOIN t_mtr_outlet oo
		ON mm.partner_id = oo.outlet_id
		JOIN t_mtr_territory tt
		ON oo.territory_id = tt.territory_id
		AND tt.parent_id = $clusterId
		WHERE mm.user_id = '$merchan_id' 
		-- AND mm.is_posm = 0
		AND mm.merchandiser_date = '$date'
		ORDER BY mm.merchandiser_date DESC, mm.created_on DESC, outlet_name ASC
		limit 10 offset $row;";
		$query = $this->db->query($sql);
	   
		return $query->result_array();

	}

	public function getOutletVisits($date=null, $merchan_id=null, $clusterId, $row){
		  //query get outletVisit
		$sql="SELECT
			mm.merchandiser_id 
			, mm.partner_id
			, oo.outlet_name
			, upper(concat_ws(', ', oo.address, oo.city)) as address
			, mm.keyword as key
			, mm.img_outlet_with_keyword as img
			,
			(select to_char(tj.check_in_date, 'YYYY-mm-dd HH24:II:SS') from t_trx_journey tj where tj.user_id=mm.user_id and tj.channel_id=mm.partner_id and 
				date(tj.check_in_date)=mm.merchandiser_date and type='checkin' ORDER BY tj.check_in_date desc limit 1) as checkin
            ,
			COALESCE((select to_char(tj1.check_in_date,'YYYY-mm-dd HH24:II:SS') 
			from t_trx_journey tj1 where tj1.user_id=mm.user_id and tj1.channel_id=mm.partner_id and date(tj1.check_in_date)=mm.merchandiser_date and type='checkout' ORDER BY tj1.check_in_date desc limit 1),'') as checkout

			FROM t_trx_merchandiser mm
			
			JOIN t_mtr_outlet oo
				ON mm.partner_id = oo.outlet_id
			JOIN t_mtr_territory tt
				ON oo.territory_id = tt.territory_id
				AND tt.parent_id =$clusterId
			
			WHERE mm.user_id = '$merchan_id'
			AND mm.is_posm = 1
			AND mm.merchandiser_date = '$date'
			ORDER BY mm.merchandiser_date DESC, mm.created_on DESC, outlet_name ASC
			limit 10 offset $row;";
			$query = $this->db->query($sql);
			return $query->result_array();
		


	}

public function getOutlets_($date=null, $merchan_id=null, $clusterId, $id, $row)
	{
		//query getFullOutlets
		/*
		*
		*/
		$id = strtolower($id);
		$type = $this->input->post('type');
		// $img_Remark = 'dd.img_'.$id.'_remark'; 
		$sql ="SELECT
		mm.partner_id
		, oo.outlet_name
		, upper(concat_ws(', ', oo.address, oo.city)) as address
		, dd.img as img
		, dd.img_remark as img_remark
		FROM t_trx_merchandiser mm
		JOIN t_trx_merchandiser_detail dd
			ON mm.merchandiser_id = dd.merchandiser_id
		JOIN t_mtr_outlet oo
			ON mm.partner_id = oo.outlet_id
		JOIN t_mtr_territory tt
			ON oo.territory_id = tt.territory_id
			AND tt.parent_id = $clusterId
		WHERE mm.user_id = '$merchan_id'
		AND mm.is_posm = 1
		AND mm.merchandiser_date = '$date'
		AND dd.img_type = '$type'
		ORDER BY mm.merchandiser_date DESC, mm.created_on DESC, oo.outlet_name ASC
		limit 10 offset $row;";
		$query = $this->db->query($sql);
			return $query->result_array();
	}
	
}